package data;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import utils.IdGenerator;

import com.mysql.jdbc.PreparedStatement;
import logic.Destination;
import logic.User;
import logic.Volunteer;

public class JDBCVolunteerDAO implements IVolunteerDAO{

	IVolunteerDAO vdao;
	
	
	public boolean registerInActivity(String userId, String activityId) {
		
		boolean registrado = false;
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stm = null;
        ResultSet result = null;
        String id = IdGenerator.getInstance().getKey();
        
        String sql = "INSERT INTO volunterrshistoy (id,iduser,idactivity) VALUES (?,?,?)";
        String sql2 = "UPDATE users SET isVolunteer = true WHERE id = ?";
        String sql3 = "UPDATE activities SET subscribevolunteers = subscribevolunteers + 1 WHERE id = ?";
        try {
        	
        	
        	
            stm = (PreparedStatement) conn.prepareStatement(sql);            
            stm.setString(1, id);
            stm.setString(2, userId);
            stm.setString(3, activityId);
            stm.executeUpdate();
            
            stm = (PreparedStatement) conn.prepareStatement(sql2);
            stm.setString(1, userId);
            stm.executeUpdate();
            
            stm = (PreparedStatement) conn.prepareStatement(sql3);
            stm.setString(1, userId);
            stm.executeUpdate();
            
            registrado = true;
       
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null) {
                    result.close();
                }

                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
            }
        }

        return registrado;
    }

	
	public List<Volunteer> listVolunteers() {
		// TODO Auto-generated method stub
		return null;
	}

	
	public boolean cancelRegistration(String userId, String activityId) {
		boolean cancelarRegistro = false;
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stm = null;
        ResultSet result = null;
        
        String sql = "DELETE FROM volunterrhistoy WHERE iduser = ? AND idactivity = ?";
        String sql2 = "UPDATE users SET isVolunteer = false WHERE id = ?";
        String sql3 = "UPDATE activities SET subscribevolunteers = subscribevolunteers - 1 WHERE id = ?";
        try {
            stm = (PreparedStatement) conn.prepareStatement(sql);            
            stm.setString(1, userId);
            stm.setString(2,activityId);
            
            result = stm.executeQuery();
            
            stm = (PreparedStatement) conn.prepareStatement(sql2);
            stm.setString(1, userId);
            result = stm.executeQuery();
            
            stm = (PreparedStatement) conn.prepareStatement(sql3);
            stm.setString(1, userId);
            result = stm.executeQuery();
            
            cancelarRegistro = true;
       
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null) {
                    result.close();
                }

                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
            }
        }

        return cancelarRegistro;
    }
		


	public List<Destination> selectNearestDestinies(User u) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        List<Destination> lo = new LinkedList<Destination>();
        String sql = "SELECT * FROM destinations WHERE continent = ? ";

        try {
            stmt = (PreparedStatement) conn.prepareStatement(sql);
            stmt.setString(1, u.getContinent());
            result = stmt.executeQuery();

            while (result.next()) {
            	Destination d = new Destination();
            	
            	d.setId(result.getString("id"));
            	d.setNombre(result.getString("name"));
            	d.setPriority(result.getInt("priority"));
            	d.setProfits(result.getFloat("profits"));
            	d.setContinent(result.getString("continent"));

                lo.add(d);
                
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return lo;
    }
	
	
}
